package com.quanquan.store.repository;

import com.quanquan.store.entity.ShopMall;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @ClassName:ShopMallRepository
 * @Description:TODO
 * @Author:xiongjunbao
 * @Date:2019/8/12 13:36
 * @Version 1.0
 */
public interface ShopMallRepository extends JpaRepository<ShopMall, String>, JpaSpecificationExecutor<ShopMall> {

    /**
     * 查找积分商城中有效的券
     *
     * @param statusRemove
     * @param statusShow
     * @return
     */
    @Query(value = "select * from oo_shop_mall where status_remove = ?1 and status_show = ?2 and now() between show_time_start and show_time_end", nativeQuery = true)
    List<ShopMall> findByStatusRemoveAndStatusShow(Integer statusRemove, Integer statusShow);


    /**
     * (后台用，通过id查找未删除的记录)
     *
     * @param id
     * @return
     */
    @Query(value = "select * from oo_shop_mall where status_remove = 0 and id = ?1", nativeQuery = true)
    ShopMall findByStatusRemoveAndId(String id);


    /**
     * 根据券类型和支付类型查找（俩类型都为空）
     *
     * @return
     */
    @Query(value = "SELECT s.* FROM oo_coupon c INNER JOIN oo_shop_mall s WHERE c.id = s.coupon_id AND c.status = 1 AND s.status_show = 0 AND s.status_remove = 0 order by s.remain_total desc LIMIT ?1,?2", nativeQuery = true)
    List<ShopMall> queryCouponList(Integer count, Integer size);

    /**
     * 根据券类型和支付类型查找（类型为空）
     *
     * @return
     */
    @Query(value = "SELECT s.* FROM oo_coupon c INNER JOIN oo_shop_mall s WHERE c.id = s.coupon_id AND c.status = 1 AND s.status_show = 0 AND s.status_remove = 0 AND s.value_type = ?1 order by s.remain_total desc LIMIT ?2,?3", nativeQuery = true)
    List<ShopMall> queryCouponListByValueType(Integer valueType, Integer count, Integer size);

    /**
     * 根据券类型和支付类型查找（支付类型为空）
     *
     * @return
     */
    @Query(value = "SELECT s.* FROM oo_coupon c INNER JOIN oo_shop_mall s WHERE c.id = s.coupon_id AND c.status = 1 AND s.status_show = 0 AND s.status_remove = 0 AND c.type = ?1 order by s.remain_total desc LIMIT ?2,?3", nativeQuery = true)
    List<ShopMall> queryCouponListByType(Integer type, Integer count, Integer size);

    /**
     * 根据券类型和支付类型查找（俩类型都不为空）
     *
     * @return
     */
    @Query(value = "SELECT s.* FROM oo_coupon c INNER JOIN oo_shop_mall s WHERE c.id = s.coupon_id AND c.status = 1 AND s.status_show = 0 AND s.status_remove = 0 AND s.value_type = ?1 and c.type = ?2 order by s.remain_total desc LIMIT ?3,?4", nativeQuery = true)
    List<ShopMall> queryCouponListByTypeAndValueType(Integer type, Integer valueType, Integer count, Integer size);

    /**
     * 热门商品
     *
     * @return
     */
    @Query(value = "SELECT s.* FROM oo_coupon c INNER JOIN oo_shop_mall s WHERE c.id = s.coupon_id AND c.status = 1 AND s.status_show = 0 AND s.status_remove = 0 and c.status_hot = ?1 order by s.remain_total desc LIMIT ?2,?3", nativeQuery = true)
    List<ShopMall> queryCouponListHot(Integer statusHot, Integer count, Integer size);

    /**
     * 搜索商品
     *
     * @return
     */
    @Query(value = "SELECT s.* FROM oo_coupon c INNER JOIN oo_shop_mall s WHERE c.id = s.coupon_id AND c.status = 1 AND s.status_show = 0 AND s.status_remove = 0 and LOCATE(?1,c.name)>0 order by s.remain_total desc LIMIT ?2,?3", nativeQuery = true)
    List<ShopMall> queryCouponListCouponName(String couponName, Integer count, Integer size);


    /**
     * 生成订单时，先将券数扣除
     *
     * @param shopMallId
     * @return
     */
    @Transactional
    @Modifying
    @Query(value = "update oo_shop_mall set remain_total = remain_total - ?2 where id = ?1 and remain_total >=?2", nativeQuery = true)
    public int updateRemainTotal(String shopMallId, Integer count);

}
